package com.hyper_yang._01_jdbc.prob;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/*
    批处理:
 */
public class BatchTest {
    @Test
    public void testBatch() throws SQLException {
        // todo: 循环往 t_account表中，插入 1万条数据
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test1", "root", "abc123");

        String sql = "insert into t_account(id,name,balance) values(?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setObject(1, 0);
            preparedStatement.setObject(2, "测试" + i);
            preparedStatement.setObject(3, 1000 + i);
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println(end - start + "ms"); // 20767 ms
        preparedStatement.close();
        connection.close();
    }

    // 优化
    @Test
    public void testBatch2() throws SQLException {
        // todo: 循环往 t_account表中，插入 1万条数据
        // todo: 在连接数据库的字符中 + 批处理参数配置 ?rewriteBatchedStatements=true
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test1?rewriteBatchedStatements=true", "root", "abc123");

        String sql = "insert into t_account(id,name,balance) values(?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setObject(1, 0);
            preparedStatement.setObject(2, "测试" + i);
            preparedStatement.setObject(3, 1000 + i);
            // todo: 将 sql语句添加到批处理队列中 (不执行)
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println(end - start + "ms"); // 157 ms
        preparedStatement.close();
        connection.close();
    }
}
